from pymysql import Connection
from datetime import datetime
import os


class LogDataImporter:
    def __init__(self):
        self.conn = None
        self.filename = "server_logs.txt"

    def connect_db(self):
        """连接数据库"""
        try:
            self.conn = Connection(
                host='localhost',
                port=3306,
                user='root',
                password='root',
                database='db3',
                charset='utf8'
            )
            print("数据库连接成功")
            return True
        except Exception as e:
            print(f"数据库连接失败: {e}")
            return False

    def close_connection(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None
            print("数据库连接已关闭")

    def check_table_structure(self):
        """检查表结构"""
        if not self.conn:
            return False

        cursor = self.conn.cursor()
        try:
            # 检查journal表的结构
            cursor.execute("DESCRIBE journal")
            columns = cursor.fetchall()
            column_names = [col[0] for col in columns]

            print("journal表结构:")
            for col in columns:
                print(f"  列名: {col[0]}, 类型: {col[1]}, 是否可为空: {col[2]}, 键: {col[3]}")

            # 检查必要的列是否存在
            required_columns = ['Jid', 'occurrenceTime', 'log_type_id', 'details']
            for col in required_columns:
                if col not in column_names:
                    print(f"错误：缺少必要的列 '{col}'")
                    return False

            return True
        except Exception as e:
            print(f"检查表结构失败: {e}")
            return False
        finally:
            cursor.close()

    def check_tables_exist(self):
        """检查表是否存在"""
        if not self.conn:
            return False

        cursor = self.conn.cursor()
        try:
            # 检查log_type表是否存在
            cursor.execute("SHOW TABLES LIKE 'log_type'")
            log_type_exists = cursor.fetchone() is not None

            # 检查journal表是否存在
            cursor.execute("SHOW TABLES LIKE 'journal'")
            journal_exists = cursor.fetchone() is not None

            if not log_type_exists or not journal_exists:
                print("错误：数据库表不存在，请先运行主程序创建表结构")
                return False

            # 检查表结构
            if not self.check_table_structure():
                return False

            # 检查log_type表中是否有数据
            cursor.execute("SELECT COUNT(*) FROM log_type")
            count = cursor.fetchone()[0]
            if count == 0:
                print("错误：log_type表中没有数据，请先运行主程序初始化数据")
                return False

            return True
        except Exception as e:
            print(f"检查表失败: {e}")
            return False
        finally:
            cursor.close()

    def get_log_type_mapping(self):
        """获取日志类型映射"""
        if not self.conn:
            return None

        cursor = self.conn.cursor()
        try:
            cursor.execute("SELECT id, type FROM log_type")
            results = cursor.fetchall()
            return {row[1]: row[0] for row in results}
        except Exception as e:
            print(f"获取日志类型映射失败: {e}")
            return None
        finally:
            cursor.close()

    def import_log_data(self):
        """导入日志数据"""
        # 检查文件是否存在
        if not os.path.exists(self.filename):
            print(f"错误：文件 {self.filename} 不存在")
            print("请先创建 server_logs.txt 文件并添加日志内容")
            return False

        # 连接数据库
        if not self.connect_db():
            return False

        # 检查表是否存在
        if not self.check_tables_exist():
            self.close_connection()
            return False

        # 获取日志类型映射
        type_mapping = self.get_log_type_mapping()
        if not type_mapping:
            print("错误：无法获取日志类型映射")
            self.close_connection()
            return False

        print("开始导入日志数据...")
        print(f"日志类型映射: {type_mapping}")

        cursor = None
        try:
            # 读取文件内容
            with open(self.filename, 'r', encoding='utf-8') as file:
                lines = file.readlines()

            # 解析每行数据
            log_entries = []
            for line_num, line in enumerate(lines, 1):
                line = line.strip()
                if not line:
                    continue

                parts = line.split(',', 2)  # 最多分割成3部分
                if len(parts) < 3:
                    print(f"警告：第 {line_num} 行格式错误，跳过: {line}")
                    continue

                timestamp_str, log_type, details = parts

                # 解析时间戳
                try:
                    timestamp = datetime.strptime(timestamp_str, "%Y-%m-%d %H:%M:%S")
                except ValueError:
                    print(f"警告：第 {line_num} 行时间格式错误，跳过: {line}")
                    continue

                # 检查日志类型是否有效
                if log_type not in type_mapping:
                    print(f"警告：第 {line_num} 行有未知的日志类型 '{log_type}'，跳过: {line}")
                    continue

                log_entries.append((timestamp, log_type, details))

            if not log_entries:
                print("文件中没有有效的日志记录")
                return False

            print(f"找到 {len(log_entries)} 条有效日志记录")

            # 插入日志记录
            cursor = self.conn.cursor()
            insert_sql = "INSERT INTO journal (occurrenceTime, log_type_id, details) VALUES (%s, %s, %s)"
            inserted_count = 0

            for i, (timestamp, log_type, details) in enumerate(log_entries, 1):
                log_type_id = type_mapping[log_type]
                try:
                    cursor.execute(insert_sql, (timestamp, log_type_id, details))
                    inserted_count += 1
                    if i % 5 == 0:  # 每5条记录显示一次进度
                        print(f"已处理 {i}/{len(log_entries)} 条记录")
                except Exception as e:
                    print(f"插入第 {i} 条记录失败: {e}")
                    print(f"记录内容: {timestamp}, {log_type_id}, {details}")

            self.conn.commit()
            print(f"成功导入 {inserted_count} 条日志记录")
            return True

        except FileNotFoundError:
            print(f"文件 {self.filename} 不存在")
            return False
        except Exception as e:
            self.conn.rollback()
            print(f"导入日志数据失败: {e}")
            import traceback
            traceback.print_exc()
            return False
        finally:
            if cursor:
                cursor.close()
            self.close_connection()


def create_log_file():
    """创建日志文件"""
    log_content = """2025-03-10 08:15:22,ERROR,数据库连接超时，重试 3 次失败
2025-03-10 08:30:45,WARN,磁盘使用率超过 80%
2025-03-10 09:00:11,INFO,用户登录成功: user=admin
2025-03-10 09:15:33,ERROR,API 接口返回 500 错误
2025-03-10 09:30:22,DEBUG,缓存刷新完成
2025-03-10 10:00:01,CRITICAL,主数据库宕机！
2025-03-10 10:15:44,WARN,网络延迟升高至 200ms
2025-03-10 10:30:12,INFO,定时任务执行完成
2025-03-10 11:00:55,ERROR,文件上传失败：权限不足
2025-03-10 11:15:23,DEBUG,SQL 查询耗时: 120ms
2025-03-10 11:30:44,WARN,内存使用率接近上限
2025-03-10 12:00:00,INFO,系统重启完成
2025-03-10 12:15:11,ERROR,认证服务不可用
2025-03-10 12:30:22,CRITICAL,备用节点同步失败
2025-03-10 13:00:33,WARN,SSL 证书即将过期
2025-03-10 13:15:44,INFO,新用户注册: user=test01
2025-03-10 13:30:55,ERROR,数据校验失败：格式错误
2025-03-10 14:00:01,DEBUG,心跳包发送成功
2025-03-10 14:15:22,WARN,API 调用频率过高
2025-03-10 14:30:33,INFO,日志归档完成"""

    try:
        with open("server_logs.txt", "w", encoding="utf-8") as file:
            file.write(log_content)
        print("已创建 server_logs.txt 文件")
        return True
    except Exception as e:
        print(f"创建文件失败: {e}")
        return False


def check_database_structure():
    """检查数据库结构并创建必要的表"""
    conn = None
    try:
        conn = Connection(
            host='localhost',
            port=3306,
            user='root',
            password='root',
            database='db3',
            charset='utf8'
        )

        cursor = conn.cursor()

        # 检查表是否存在
        cursor.execute("SHOW TABLES LIKE 'log_type'")
        log_type_exists = cursor.fetchone() is not None

        cursor.execute("SHOW TABLES LIKE 'journal'")
        journal_exists = cursor.fetchone() is not None

        if not log_type_exists or not journal_exists:
            print("检测到表不存在，正在创建表结构...")

            # 创建log_type表
            create_log_type_sql = """
                                  CREATE TABLE IF NOT EXISTS log_type( 
                                      id INT PRIMARY KEY AUTO_INCREMENT,
                                      type VARCHAR(20),
                                      type_description TEXT,
                                      level INT NOT NULL COMMENT '1-低, 2-中, 3-高, 4-极高'
                                  )
                                  """

            # 创建journal表
            create_journal_sql = """
                                 CREATE TABLE IF NOT EXISTS journal(
                                     Jid INT PRIMARY KEY AUTO_INCREMENT,
                                     occurrenceTime DATETIME,
                                     log_type_id INT,
                                     details TEXT,
                                     FOREIGN KEY(log_type_id) REFERENCES log_type(id)
                                )
                                """

            cursor.execute(create_log_type_sql)
            cursor.execute(create_journal_sql)
            conn.commit()
            print("表创建成功！")

            # 初始化日志类型数据
            log_types = [
                ('ERROR', '系统错误', 3),
                ('WARN', '警告信息', 2),
                ('INFO', '普通信息', 1),
                ('DEBUG', '调试日志', 1),
                ('CRITICAL', '严重故障', 4)
            ]

            insert_sql = """
                         INSERT INTO log_type (type, type_description, level)
                         VALUES (%s, %s, %s)
                         """

            cursor.executemany(insert_sql, log_types)
            conn.commit()
            print("日志类型数据初始化完成！")

        else:
            print("表结构已存在")

        cursor.close()
        return True

    except Exception as e:
        print(f"检查数据库结构失败: {e}")
        return False
    finally:
        if conn:
            conn.close()


# 主程序
if __name__ == "__main__":
    print("日志数据导入程序")
    print("=" * 50)

    # 检查数据库结构
    if not check_database_structure():
        print("数据库结构检查失败，程序退出")
        exit()

    # 检查文件是否存在，如果不存在则创建
    if not os.path.exists("server_logs.txt"):
        print("检测到 server_logs.txt 文件不存在")
        if input("是否创建文件？(y/n): ").lower() == 'y':
            if not create_log_file():
                print("程序退出")
                exit()
        else:
            print("请手动创建 server_logs.txt 文件后再运行程序")
            print("程序退出")
            exit()

    # 导入数据
    importer = LogDataImporter()
    if importer.import_log_data():
        print("数据导入完成！")
    else:
        print("数据导入失败！")

    input("按回车键退出...")